﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Specialized;
using WP.DB;
using WP.RM.DataEntity;
using WP.Commons;

namespace WP.RM.DataAccess
{
    public class RMCategorySetBOMHdrDAL : IDataAccess
    {

        string strDetails = @"RowSeqNo ,SeqNo,WKID,WKName,WKRemark,CategoryID,CategoryName,SetID,SetName,
                              IsKeyWK, isInputWK ,FItemID, ItemNo, ItemName,SupNo,SupName, Price, PriceUnit,
                              Qty, WastageRate,DetailCost, RatedCost,LabourCost, TotalCost,WastageCost";

        string strLabourCost = @"RowSeqNo ,SeqNo,WKID,WKName,WKRemark,CategoryID,CategoryName,SetID,SetName,FItemID,ItemNo,
                                 CalUnit ,	CutType ,	TotalQty,	StandardQty ,	SpendHours,	StandardWage ,	StandardWage26 ,	
                                 UnitCost ,	Remark , WorkCost , IsKeyWK
                                ";

        public RMCategorySetBOMHdrDAL()
		{
		}
        #region Select List
        public string SQLList(int iParameter)
        {
            string strSQL = @"SELECT RecId, CategoryName, SetName,Remark FROM rmCategorySetBOMHdr order by CategoryName,SetName ";
            return strSQL;
        }
        #endregion

        #region Fields
        public string Fields
        {
            get
            {
//                return @"   CategoryName, SetName,Remark,TotalDetailCost,TotalLabourCost,TotalWastageCost,TotalCost,
//                            LabelUnitQty,	LabelLength,	LabelWidth ,	PrintLength,
//	                        PrintWidth ,	LaminatingLength ,	LaminatingWidth ,	CutFactor ,
//	                        PrintUseQty, 	ContainQty ,	PackageQty ,	InnerDiameterLength,LabelSize,
//	                        InnerDiameterWidth ,	InnerDiameterHeight,	InnerOutsideDiff ,	MiddleWidth,	
//                            BurrWidth ,	OtherPrintWidth ,	OtherPrintBackWidth ,	UseIronNailQty ,	
//                            LockingWireQty ,	LockingWireLength , WorkCost , PagePrintWidth,PagePrintLength,approval,approvalDate,approvalPerson ";

                return @"   CategoryID,CategoryName,SetID, SetName,Remark,TotalDetailCost,TotalLabourCost,TotalWastageCost,TotalCost,
                            WorkCost , approval,approvalDate,approvalPerson ";
            }
        }
        #endregion

        #region BOM Grid
        public bool UpdateHdrbyBOMDetail(DBAccess dbcon, string strCategoryID, string strSetID )
        {
            double dblTotalDetailCost = 0;
            double dblTotalLabourCost = 0;
            double dblTotalWastageCost = 0;

            string strFieldName = "CategoryID, SetID";

            string strSQL = "select sum(DetailCost),sum(LabourCost),sum(WastageCost) from rmCategorySetBOMDetail where CategoryID='" + strCategoryID + "' and SetID='" + strSetID + "'";
            DataRow drTotal = dbcon.GetDataFirstRow(strSQL);
            if (drTotal != null)
            {
                 dblTotalDetailCost = CvtFunc.ToDouble(drTotal[0]);
                 dblTotalLabourCost = CvtFunc.ToDouble(drTotal[1]);
                 dblTotalWastageCost = CvtFunc.ToDouble(drTotal[2]);
            }
            double dblTotalCost = dblTotalDetailCost + dblTotalLabourCost + dblTotalWastageCost ;


            strSQL = "select * from rmCategorySetBOMHdr where CategoryID='{0}' and SetID='{1}'";
            strSQL = string.Format(strSQL, strCategoryID, strSetID);
            DataRow drHdr = dbcon.GetDataFirstRow(strSQL);
            if ( drHdr ==null )
            {
                DataFunctions.UpdateTable(dbcon,  strFieldName  , "rmCategorySetBOMHdr", drHdr, FormStatus.AddNew); 
            }
            else
            {
                DataFunctions.UpdateTable(dbcon, strFieldName, "rmCategorySetBOMHdr", drHdr, FormStatus.Update ); 
            }

            strSQL = @" update rmCategorySetBOMHdr set TotalDetailCost='{2}',TotalLabourCost='{3}',TotalWastageCost='{4}',TotalCost='{5}'
                        where CategoryID='{0}' and SetID='{1}' ";
            strSQL = string.Format(strSQL, strCategoryID, strSetID, dblTotalDetailCost, dblTotalLabourCost,
                                            dblTotalWastageCost, dblTotalCost);
            dbcon.ExecuteSql(strSQL);
            return true;
        }

        public bool UpdateBOMDetail(DBAccess dbcon, DataRow drBOM)
        {
            try
            {
                if (!UpdateCost( dbcon, drBOM )) return false;
                if (CvtFunc.ToDouble(drBOM["RecID"]) > 0)
                {
                    DataFunctions.UpdateTable(dbcon, strDetails, "rmCategorySetBOMDetail", drBOM, FormStatus.Update);
                }
                else
                {
                    DataFunctions.UpdateTable(dbcon, strDetails, "rmCategorySetBOMDetail", drBOM, FormStatus.AddNew);
                }
                
                string strCategoryID = CvtFunc.ToString(drBOM["CategoryID"]);

                UpdateHdrbyBOMDetail(dbcon, CvtFunc.ToString (drBOM["CategoryID"]), CvtFunc.ToString ( drBOM["SetID"]));
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
                return false;
            }
            return true;
        }


        public bool UpdateCost(DBAccess dbcon, DataRow drBOM)
        {
            CalQty( dbcon, drBOM  );
            CalQtyWastage(dbcon, drBOM);
            CalWastageCost(dbcon, drBOM);
            CalDetailCost(dbcon, drBOM);
            CalTotalCost(dbcon, drBOM);
            return true;
        }

        public bool CalQty(DBAccess dbcon, DataRow drBOM)   //用于计算定额用量
        {
            drBOM["Qty"] = CvtFunc.ToDouble (drBOM["Qty"] );
            return true;
        }

        public bool CalQtyWastage(DBAccess dbcon, DataRow drBOM)  //用于计算损耗数量
        {
            drBOM["QtyWastage"] = CvtFunc.ToDouble(drBOM["WastageRate"]) * CvtFunc.ToDouble (drBOM["Qty"])*0.01;
            return true;
        }

        public bool CalWastageCost(DBAccess dbcon, DataRow drBOM)  //用于计算损耗成本
        {
            drBOM["WastageCost"] = CvtFunc.ToDouble(drBOM["QtyWastage"]) * CvtFunc.ToDouble(drBOM["Price"]);
            return true;
        }

        public bool CalDetailCost(DBAccess dbcon, DataRow drBOM)  //用于计算定额成本
        {
            drBOM["DetailCost"]= CvtFunc.ToDouble (drBOM["Qty"])* CvtFunc.ToDouble (drBOM["Price"]);
            return true;
        }

        public bool CalTotalCost(DBAccess dbcon, DataRow drBOM)  //用于计算总成本
        {
            drBOM["TotalCost"] = CvtFunc.ToDouble(drBOM["DetailCost"]) + CvtFunc.ToDouble(drBOM["LabourCost"]) + CvtFunc.ToDouble(drBOM["WastageCost"]);
            return true;
        }

        #endregion


        #region OtherCost Grid

        public bool UpdateLabourCostDetail(DBAccess dbcon, DataRow drLabourCost)
        {
            try
            {
                CalUnitCost(dbcon, drLabourCost);
                if (CvtFunc.ToDouble(drLabourCost["RecID"]) > 0)
                {
                    DataFunctions.UpdateTable(dbcon, strLabourCost, "rmCategorySetBOMLabourCost", drLabourCost, FormStatus.Update);
                }
                else
                {
                    DataFunctions.UpdateTable(dbcon, strLabourCost , "rmCategorySetBOMLabourCost", drLabourCost, FormStatus.AddNew);
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public void CalUnitCost(DBAccess dbcon, DataRow drLabourCost)
        {
            double dblStandardQty = CvtFunc.ToDouble(drLabourCost["StandardQty"]);      //额定产量
            double dblStandardWage = CvtFunc.ToDouble(drLabourCost["StandardWage"]);    //固定工资  
            double dblStandardWage26 = CvtFunc.ToDouble(drLabourCost["StandardWage26"]);    //26天标准工资 

            double dblUnitCost = 0;
            if (dblStandardQty == 0)
                dblUnitCost = 0;
            else
                dblUnitCost = (dblStandardWage26 - dblStandardWage / 22d * 26) / dblStandardQty / 26d;
            
            drLabourCost["UnitCost"] = dblUnitCost.ToString ("f6");
        }

        #endregion

        #region CopyTo
        public bool CopytoNewVer(DBAccess dbcon, DataRow drOldHdr,string strDestCategory, string strDestSet )
        {
            try
            {
                // init HDR
                
                if (drOldHdr == null) return false;
                string strCategoryName = "", strSetName = "", strProductName = "", strOldVersion = "";
                string strCategoryID = "", strSetID = "", strProductFItemID = "";
                bool blnAlreadyExist = false;
                string strSQL = "";
                strCategoryID = CvtFunc.ToString(drOldHdr["CategoryID"]);
                strSetID = CvtFunc.ToString(drOldHdr["SetID"]);
                strProductFItemID = CvtFunc.ToString(drOldHdr["ProductFItemID"]);

                strCategoryName = CvtFunc.ToString(drOldHdr["CategoryName"]);
                strSetName = CvtFunc.ToString(drOldHdr["SetName"]);
                strProductName = CvtFunc.ToString(drOldHdr["ProductName"]);

                string strFieldsCopy = Fields + ", Quantity,ProductLength,ProductWidth,Pages,PaperHeight,SetSize";
                DataRow drHdr = dbcon.GetDataTable("select top 0 * from rmCategorySetBOMHdr").NewRow();
                {
                    strSQL = "select * from rmCategorySetBOMHdr where CategoryID='" + strDestCategory + "' and SetID='" + strDestSet + "'";
                    DataRow drDest = dbcon.GetDataFirstRow(strSQL);
                    if (drDest != null)
                    {
                        blnAlreadyExist = true;
                        if (MsgBoxEx.Show("目标BOM已经存在，如果继续系统会覆盖原BOM，是否继续?", "", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No)
                            return false;
                        else
                        {

                        }
                    }
                    else
                    {
                        foreach (DataColumn drCol in drOldHdr.Table.Columns)
                        {
                            if (drCol.ToString().Trim().ToUpper() == "RECID" || drCol.ToString().Trim().ToUpper() == "RECSTAMP")
                            {
                            }
                            else
                            {
                                drHdr[drCol.ToString()] = drOldHdr[drCol.ToString()];
                            }
                        }
                        drHdr["CategoryID"] = strDestCategory;
                        drHdr["SetID"] = strDestSet;

                        drHdr["CategoryName"] =DataFunctions.GetFieldValue( dbcon,"rmCategory","CategoryName","CategoryID='"+ strDestCategory +"'")  ;
                        drHdr["SetName"] = DataFunctions.GetFieldValue(dbcon, "rmSet", "SetName", "SetID='" + strDestSet + "'");
                        DataFunctions.UpdateTable(dbcon, strFieldsCopy, "rmCategorySetBOMHdr", drHdr, FormStatus.AddNew);
                    }
                }

                strSQL = "select * from rmCategorySetBOMDetail A  where A.CategoryID='{0}' and A.SetID='{1}' order by A.RowSeqNo";
                strSQL = string.Format(strSQL, strCategoryID, strSetID);
                DataTable dtOld = dbcon.GetDataTable(strSQL);
                foreach (DataRow dr in dtOld.Rows)
                {
                    DataRow drBOM = dbcon.GetDataSet("select top 0 * from rmCategorySetBOMDetail").Tables[0].NewRow();

                    drBOM["SeqNo"] = dr["SeqNo"].ToString().TrimEnd();
                    drBOM["RowSeqNo"] = dr["RowSeqNo"].ToString().Trim();
                    drBOM["WKID"] = dr["WKID"].ToString().TrimEnd();
                    drBOM["WKName"] = DataFunctions.GetFieldValue(dbcon, "rmWorkingProcedure", "WorkingProcedureName", "WorkingProcedureID='" + dr["WKID"]+ "'");

                    drBOM["CategoryID"] = strDestCategory;
                    drBOM["SetID"] = strDestSet;

                    drBOM["CategoryName"] = DataFunctions.GetFieldValue(dbcon, "rmCategory", "CategoryName", "CategoryID='" + strDestCategory + "'");
                    drBOM["SetName"] = DataFunctions.GetFieldValue(dbcon, "rmSet", "SetName", "SetID='" + strDestSet + "'");

                    drBOM["FItemID"] = CvtFunc.ToString(dr["FItemID"]);
                    drBOM["ItemNo"] = DataFunctions.GetFieldValue ( dbcon,"iminvmaster","itemno","FitemID='"+ CvtFunc.ToString(dr["FItemID"]) +"'");
                    drBOM["ItemName"] = DataFunctions.GetFieldValue(dbcon, "iminvmaster", "itemname", "FitemID='" + CvtFunc.ToString(dr["FItemID"]) + "'"); 
                    drBOM["SupNo"] = CvtFunc.ToString(dr["SupNo"]);
                    drBOM["SupName"] = CvtFunc.ToString(dr["SupName"]);
                    drBOM["LabourCost"] = BaseFunctions.FormatNumeric(dr["LabourCost"], "Ns.4");
                    drBOM["Price"] =CvtFunc.ToDouble ( DataFunctions.GetFieldValue(dbcon, "iminvmaster", "marketcost", "itemno='" + drBOM["ItemNo"].ToString() + "' and supno='" + drBOM["SupNo"].ToString() + "'")); //BaseFunctions.FormatNumeric(dr["Price"], "Ns.3");
                    drBOM["PriceUnit"] = CvtFunc.ToString(dr["PriceUnit"]);
                    drBOM["Qty"] = BaseFunctions.FormatNumeric(CvtFunc.ToDouble(dr["Qty"]).ToString("F8"), "Ns.8");
                    drBOM["QtyWastage"] = BaseFunctions.FormatNumeric(dr["QtyWastage"], "Ns.2");
                    drBOM["WastageRate"] = BaseFunctions.FormatNumeric(dr["WastageRate"], "Ns.2");
                    drBOM["TotalCost"] = BaseFunctions.FormatNumeric(dr["TotalCost"], "Ns.2");
                    drBOM["DetailCost"] = BaseFunctions.FormatNumeric(dr["DetailCost"], "Ns.4");
                    drBOM["IsInputWK"] = dr["IsInputWK"].ToString();
                    drBOM["IsKeyWK"] = dr["IsKeyWK"].ToString();
                    if (!UpdateBOMDetail(dbcon, drBOM))
                    {
                        return false;
                    }

                    // Init  rmWKProductFormula  
                    strSQL = "select * from rmWKFormula where BOMRecID='" + CvtFunc.ToString(dr["RecID"]) + "'";
                    DataTable dtFormula = dbcon.GetDataTable(strSQL);
                    foreach (DataRow drFormula in dtFormula.Rows)
                    {
                        strSQL = @"Insert Into rmWKFormula (BOMRecID,WKFormula)
                              VALUES ('{0}','{1}')";
                        strSQL = string.Format(strSQL, drBOM["RecID"], drFormula["WKFormula"]);
                        dbcon.ExecuteSql(strSQL);
                    }
                }

                strSQL = "select A.* from rmCategorySetBOMLabourCost A where A.CategoryID='{0}' and A.SetID='{1}' order by a.RowSeqNo ";
                strSQL = string.Format(strSQL, strCategoryID, strSetID);
                dtOld = dbcon.GetDataTable(strSQL);
                foreach (DataRow dr in dtOld.Rows)
                {
                    DataRow drBOM = dbcon.GetDataSet("select  top 0 * from rmCategorySetBOMLabourCost").Tables[0].NewRow();

                    drBOM["SeqNo"] = dr["SeqNo"].ToString().TrimEnd();
                    drBOM["RowSeqNo"] = dr["RowSeqNo"].ToString().Trim();

                    drBOM["WKID"] = dr["WKID"].ToString().TrimEnd();
                    drBOM["WKName"] = DataFunctions.GetFieldValue(dbcon, "rmWorkingProcedure", "WorkingProcedureName", "WorkingProcedureID='" + dr["WKID"] + "'");

                    drBOM["CategoryID"] = strDestCategory;
                    drBOM["SetID"] = strDestSet;

                    drBOM["CategoryName"] = DataFunctions.GetFieldValue(dbcon, "rmCategory", "CategoryName", "CategoryID='" + strDestCategory + "'");
                    drBOM["SetName"] = DataFunctions.GetFieldValue(dbcon, "rmSet", "SetName", "SetID='" + strDestSet + "'");
                    
                    drBOM["CalUnit"] = CvtFunc.ToString(dr["CalUnit"]);
                    drBOM["CutType"] = CvtFunc.ToString(dr["CutType"]);
                    drBOM["TotalQty"] = BaseFunctions.FormatNumeric(dr["TotalQty"], "Ns.4");
                    drBOM["StandardQty"] = BaseFunctions.FormatNumeric(dr["StandardQty"], "Ns.3");
                    drBOM["SpendHours"] = CvtFunc.ToString(dr["SpendHours"]);
                    drBOM["StandardWage"] = BaseFunctions.FormatNumeric(dr["StandardWage"], "Ns.2");
                    drBOM["StandardWage26"] = BaseFunctions.FormatNumeric(dr["StandardWage26"], "Ns.2");
                    drBOM["UnitCost"] = BaseFunctions.FormatNumeric(dr["UnitCost"], "Ns.2");
                    drBOM["Remark"] = CvtFunc.ToString(dr["Remark"]);
                    drBOM["WorkCost"] = CvtFunc.ToString(dr["WorkCost"]);
                    double dblWorkCost = DataFunctions.GetCategoryWKWorkCost(dbcon, CvtFunc.ToString(drBOM["CategoryID"]), CvtFunc.ToString(drBOM["WKID"]));
                    if (dblWorkCost > 0)
                    {
                        drBOM["WorkCost"] = BaseFunctions.FormatNumeric(dblWorkCost, "Ns.2");
                    }
                    else
                    {
                        drBOM["WorkCost"] = CvtFunc.ToString(dr["WorkCost"]);
                    }
                    drBOM["FItemID"] = CvtFunc.ToString(dr["FItemID"]);
                    drBOM["ItemNo"] = DataFunctions.GetFieldValue(dbcon, "iminvmaster", "itemno", "FitemID='" + CvtFunc.ToString(dr["FItemID"]) + "'");
                    if (!UpdateLabourCostDetail(dbcon, drBOM))
                    {
                        return false;
                    }
                    // Init  rmWKProductFormula  
                    strSQL = "select * from rmWKFormulaOtherCategorySetBOM where BOMRecID='" + CvtFunc.ToString(dr["RecID"]) + "'";
                    DataTable dtFormula = dbcon.GetDataTable(strSQL);
                    foreach (DataRow drFormula in dtFormula.Rows)
                    {
                        strSQL = @"Insert Into rmWKFormulaOtherCategorySetBOM (BOMRecID,WKFormula)
                              VALUES ('{0}','{1}')";
                        strSQL = string.Format(strSQL, drBOM["RecID"], drFormula["WKFormula"]);
                        dbcon.ExecuteSql(strSQL);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                MsgBoxEx.Show(ex.ToString());
                return false;
            }

        }

        #endregion
    }
}
